package database;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.Set;

import recipie.core.Ingredient;
import recipie.core.IngredientCollection;
import recipie.core.MesureUnit;

import category.Category;
import category.CategoryCollection;


public class IngredientManager 
{
  private static Connection  con;
  private static Statement stmt;
  private static ResultSet rs; 
  private static String query;

  //Insert an ingredient:
  public static boolean insertIngredient(Ingredient ingredient)
  {
    if((con = DatabaseManager.getDatabaseDriverAndConnect()) == null)
	  return false;
    
    try
	{
      insertIntoIngredientTable(ingredient);
	}
	catch(SQLException e)
	{
	  return false;
	}
	   
	return true;	 
  }
  
    //Delete an ingredient
	public static boolean deleteIngredient(Ingredient ingredient)
	{
	  if(ingredient == null)
	    return true;
	  
	  if((con = DatabaseManager.getDatabaseDriverAndConnect()) == null)
	    return false;
			  
	  query = "delete from ingredient where name = '" + ingredient.getName() + "';";
		 
	  try
	  {
	    stmt = DatabaseManager.createQuery(stmt, con);
		DatabaseManager.executeUpdate(stmt, query);
	  }
	  catch(SQLException e)
	  {
	    return false;
	  }
	    
	  return true;
	}
	
	//Check if an ingredient exists
	public static boolean existsIngredient(String id) 
	{
	  if((con = DatabaseManager.getDatabaseDriverAndConnect()) == null)
	    return false;
			  
	  query = "select * from ingredient where name = '" + id + "';";
		  
	  try
	  {
	    stmt = DatabaseManager.createQuery(stmt, con);
		rs = DatabaseManager.executeQuery(rs, stmt, query);
		rs.next();
		if(rs.getString("name").equals(id))	
		  return true;
	  }
	  catch(SQLException e)
	  { 
	    return false;
	  }
		  
	  return false;
	}
	
	//Load all ingredients into a IngredientCollection
	public static IngredientCollection loadIngredients() 
	{
	  if((con = DatabaseManager.getDatabaseDriverAndConnect()) == null)
	    return null;
	  
	  IngredientCollection ingredientCollection = new IngredientCollection();
	  
	  try
	  {
	    query = "select * from ingredient;";
		stmt = DatabaseManager.createQuery(stmt, con);
		rs = DatabaseManager.executeQuery(rs, stmt, query);
		
		while(rs.next())
		{
		  ingredientCollection.addIngredient(new Ingredient(rs.getString("name"), MesureUnit.valueOf(rs.getString("default_unit"))));
		}
		ingredientCollection = manageCategories(ingredientCollection);
	  }
	  catch(SQLException e)
	  { 
	    return null;
	  }
	
	  return ingredientCollection;
	}
  
  private static IngredientCollection manageCategories(IngredientCollection ingredientCollection) 
  {
    Set<String> keys = ingredientCollection.getIngredients().keySet();
    for(String k : keys)
	{
      LinkedList<Category> categories = new LinkedList<Category>();
	  String name = ingredientCollection.getIngredients().get(k).getName();
	  query = "select * from category_ingredient where ingredient_name ='" + name + "';";
	  try
	  {
	    stmt = DatabaseManager.createQuery(stmt, con);
		rs = DatabaseManager.executeQuery(rs, stmt, query);
		while(rs.next())
		{
		  categories.add(new Category(rs.getString("category_name")));	
		}
		ingredientCollection.getIngredients().get(k).setCategories(categories);
	  }
	  catch(SQLException e)
	  { 
	    return null;
	  }
	}
	  
	return ingredientCollection;
  }

  private static void insertIntoIngredientTable(Ingredient ingredient) throws SQLException
  {
    query = "insert into ingredient values ('" + ingredient.getName() + "'," + "'" + ingredient.getUnit().toString() + "');";
	stmt = DatabaseManager.createQuery(stmt, con);
	DatabaseManager.executeUpdate(stmt, query);
	
	if(ingredient.getCategories() == null)
	  return;
	for(Category c : ingredient.getCategories())
	{
	  query = "insert into category_ingredient values ('" + ingredient.getName() + "'," + "'" + c.getName() + "');";
	  stmt = DatabaseManager.createQuery(stmt, con);
	  DatabaseManager.executeUpdate(stmt, query);
	}
  }
}









